using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.ComponentModel;
using System;

namespace DBConnectionLib
{
    public class Database
    {

        protected string m_ConnectionString = "";
        protected string m_Name = "";
        protected string m_DataSourceName = "";
        protected string m_OldDatabaseName = "";
        protected string m_OldDataSourceName = "";
        protected string m_DatabaseName = "";
        protected string m_BaseDirectory = "";
        protected string m_UserName = "";
        private string m_OldUserName = "";
        private string m_OldPassword = "";
        private string m_SQLVersion = "";
        private bool m_IsDeploymentTarget = false;
        private string m_ErrorMessage = "";
  
        private string m_CommandSQL = "";

        public bool NeedsWriteTargets { get; set; }

        protected Database()
        {
        }

        public Database(string strName, string strDataSourceName, string strDatabaseName, string strBaseDirectory)
        {
            // TODO : COMPLETE 1.2.0.8 dash in Database Name (dpwoos)
            this.m_Name = strName;
            this.m_OldDatabaseName = strDatabaseName;
            this.m_DataSourceName = strDataSourceName;
            this.m_OldDataSourceName = strDataSourceName;
            this.m_DatabaseName = strDatabaseName;
            this.m_BaseDirectory = strBaseDirectory;
            //this.m_ConnectionString = "Data Source=" + strDataSourceName + ";DataBase=" + strDatabaseName + ";Integrated Security=True;Connect Timeout=0;";
            
        }

        public Database(Database oDatabase)
        {
            this.m_Name = oDatabase.m_Name;
            this.m_OldDatabaseName = oDatabase.m_OldDatabaseName;
            this.m_DataSourceName = oDatabase.m_DataSourceName;
            this.m_OldDataSourceName = oDatabase.m_OldDataSourceName;
            this.m_DatabaseName = oDatabase.m_DatabaseName;
            this.m_BaseDirectory = oDatabase.m_BaseDirectory;
            this.m_ConnectionString = oDatabase.m_ConnectionString;
            this.m_SQLVersion = oDatabase.m_SQLVersion;
            this.m_UserName = oDatabase.m_UserName;
            this.m_Password = oDatabase.m_Password;
        }


        #region Properties
        [BrowsableAttribute(true),
         CategoryAttribute("File System"),
         DescriptionAttribute("SQL7, SQL2000 or SQL2005."),
        DefaultValueAttribute(false)]
        public string SQLVersion
        {
            get { return m_SQLVersion; }
            set { m_SQLVersion = value; }
        }

        [BrowsableAttribute(true),
        CategoryAttribute("Connection Settings"),
        DefaultValueAttribute(false)]
        public string DatabaseName
        {
            get { return m_DatabaseName; }
            set
            {
                //ConnectionString = ConnectionString.Replace(";DataBase=" + this.m_OldDatabaseName, ";DataBase=" + value);
                m_DatabaseName = value;
                m_OldDatabaseName = value;
            }
        }

        [BrowsableAttribute(true),
        CategoryAttribute("Connection Settings"),
        DescriptionAttribute("The server name to connect to.  You may use 'local' or '.' or 'local\\sqlexpress' or ip address or 'servername'."),
        DefaultValueAttribute(false)]
        public string DataSourceName
        {
            get { return m_DataSourceName; }
            set
            {
                m_DataSourceName = value;
                //ConnectionString = ConnectionString.Replace("Data Source=" + m_OldDataSourceName, "Data Source=" + value);
                m_OldDataSourceName = value;
            }
        }

        [BrowsableAttribute(true),
        CategoryAttribute("Connection Settings"),
        DescriptionAttribute("The database Nick Name.  This value must be unique."),
        DefaultValueAttribute(false)]
        public string NickName
        {
            get { return m_Name; }
            set { m_Name = value; }
        }

        [BrowsableAttribute(true),
        CategoryAttribute("Connection Settings"),
        DescriptionAttribute("Optional.\nThe full connection string is required if not using trusted connections."),

        DefaultValueAttribute(false)]
        public string ConnectionString
        {
            get {
                if (String.IsNullOrEmpty(m_ConnectionString))
                {
                    string strConnString = "Data Source=" + this.DataSourceName + ";DataBase=" + this.DatabaseName + ";";
                    if (string.IsNullOrEmpty(this.UserName))
                        strConnString += "Integrated Security=True;";
                    else
                        strConnString += "Persist Security Info=True;User ID=" + this.UserName + ";Password=" + this.Password + ";";
                    strConnString += "Connect Timeout=0;";
                    m_ConnectionString = strConnString;
                }
                return m_ConnectionString;

            }
            set { m_ConnectionString = value; }
        }

        [BrowsableAttribute(true),
        CategoryAttribute("Connection Settings"),
        DescriptionAttribute("Optional"),
        DefaultValueAttribute(false)]
        public string UserName
        {
            get { return m_UserName; }
            set
            {
                m_UserName = value;
                ////;Persist Security Info=True;User ID=test_data_entry;Password=test_data_entry
                //if (!string.IsNullOrEmpty(value))
                //{
                //    ConnectionString = ConnectionString.Replace("Integrated Security=True;", "Persist Security Info=True;");
                //    if (ConnectionString.Contains("User ID="))
                //    {
                //        ConnectionString = ConnectionString.Replace("User ID=" + m_OldUserName, "User ID=" + value);
                //    }
                //    else
                //    {
                //        string strUserIdString = "User ID=" + value;
                //        ConnectionString += strUserIdString;
                //    }

                //}
                //else
                //{
                //    ConnectionString = ConnectionString.Replace("Persist Security Info=True;", "Integrated Security=True;");
                //    if (ConnectionString.Contains("User ID="))
                //    {
                //        ConnectionString = ConnectionString.Replace("User ID=" + m_OldUserName, "");
                //    }
                //}
                //m_OldUserName = value;
            }
        }
        private string m_Password;

        [BrowsableAttribute(true),
        CategoryAttribute("Connection Settings"),
        DescriptionAttribute("Optional"),
        DefaultValueAttribute(false)]

        public string Password
        {
            get { return m_Password; }
            set
            {
                m_Password = value;
                //if (!string.IsNullOrEmpty(value))
                //{
                //    if (ConnectionString.Contains(";Password="))
                //    {
                //        string strPass = ";Password=" + m_OldPassword;
                //        ConnectionString = ConnectionString.Replace(strPass, ";Password=" + value);
                //    }
                //    else
                //    {
                //        ConnectionString += ";Password=" + value;
                //    }
                //}
                //else
                //{
                //    if (ConnectionString.Contains(";Password="))
                //    {
                //        string strPass = ";Password=" + m_OldPassword;
                //        ConnectionString = ConnectionString.Replace(strPass, "");
                //    }

                //}
                //m_OldPassword = value;
            }
        }

        [BrowsableAttribute(false),
        DefaultValueAttribute(false)]
        public bool IsDeploymentTarget
        {
            get { return m_IsDeploymentTarget; }
            set { m_IsDeploymentTarget = value; }
        }

        [BrowsableAttribute(true),
CategoryAttribute("File System"),
DescriptionAttribute("Select sqlcmd.exe or osql.exe for script execution.\nYou might need to include the full path if this path is not in your environment variables.\n"),
DefaultValueAttribute(false)]
        public string CommandSQL
        {
            get { return m_CommandSQL; }
            set { m_CommandSQL = value; }
        }

        //public void SetUserName(string strUserName)
        //{
        //    this.m_UserName = strUserName;
        //    this.m_OldUserName = strUserName;
        //}

        //public void SetPassword(string strPass)
        //{
        //    this.m_Password = strPass;
        //    this.m_OldPassword = strPass;
        //}

        [BrowsableAttribute(true),
        ReadOnlyAttribute(true),
        CategoryAttribute("File System"),
        DescriptionAttribute("The output directory for all created scripts."),
        DefaultValueAttribute(false)]

        public string BaseDirectory
        {
            get { return m_BaseDirectory; }
            set { m_BaseDirectory = value; }
        }

        [BrowsableAttribute(false),
        DefaultValueAttribute(false)]
        public string ErrorMessage
        {
            get { return m_ErrorMessage; }
            set { m_ErrorMessage = value; }
        }

        #endregion Properties

        public bool RunScript(string strScriptFileName)
        {
            this.ErrorMessage = "";
            bool result = true;
            if (false == System.IO.File.Exists(strScriptFileName))
            {
                m_ErrorMessage = "File " + strScriptFileName + " does not exist.";
                throw new Exception("File " + strScriptFileName + " does not exist.");
            }

            System.Diagnostics.Process p = new System.Diagnostics.Process();
            // Redirect the output stream of the child process.
            p.StartInfo.UseShellExecute = false;
            p.StartInfo.RedirectStandardOutput = true;
            p.StartInfo.RedirectStandardError = true;
            p.StartInfo.CreateNoWindow = true;
            p.StartInfo.FileName = "sqlcmd.exe";
            p.StartInfo.Arguments = " -S " + this.DataSourceName + " -E -d " + this.DatabaseName + " -i " + strScriptFileName;
            p.Start();
            // Do not wait for the child process to exit before
            // reading to the end of its redirected stream.
            // p.WaitForExit();
            // Read the output stream first and then wait.
            string output = p.StandardOutput.ReadToEnd();
            string errors = p.StandardError.ReadToEnd();
            p.WaitForExit();

            if (errors.Length > 0)
            {
                this.ErrorMessage = errors;
                result = false;
            }
            if (output.Contains("Msg"))
            {
                this.ErrorMessage += output;
                result = false;
            }
            if (!result)
                throw new Exception(output + errors);
            return true;
        }

        public bool TestSqlClientConnection()
        {
            bool testSucceeded = false;
            try
            {

                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(this.ConnectionString))
                {
                    conn.Open();
                    testSucceeded = true;
                }


            }
            catch (Exception ex)
            {
                this.ErrorMessage = ex.ToString();
            }
            return testSucceeded;
        }
        
        public bool TestSmoConnection()
        {
            bool testSucceeded = false;
            try
            {

                Microsoft.SqlServer.Management.Smo.Database oMDatabase = GetSmoDatabase(GetSmoServer());
                if (oMDatabase == null)
                {
                    this.ErrorMessage = "Cannot find database " + this.DatabaseName + " on server " + this.DataSourceName;
                    testSucceeded = false;
                }
                else
                    testSucceeded = true;

            }
            catch (Exception ex)
            {
                this.ErrorMessage = ex.ToString();
            }
            return testSucceeded;
        }

        public bool TestOleDbConnection()
        {
            bool testSucceeded = false;
            try
            {

                using (System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection(this.ConnectionString))
                {
                    oConn.Open();
                }


            }
            catch (Exception ex)
            {
                testSucceeded = false;
                this.ErrorMessage = ex.ToString();
            }
            return testSucceeded;
        }

        public Microsoft.SqlServer.Management.Smo.Database GetSmoDatabase(Microsoft.SqlServer.Management.Smo.Server oServer)
        {
            bool dbFound = false;
            if (oServer != null)
            {
                foreach (Microsoft.SqlServer.Management.Smo.Database oDatabase in oServer.Databases)
                {
                    
                    if (oDatabase.Name.CompareTo(this.DatabaseName) == 0)
                    {
                        dbFound = true;
                        break;
                    }
                }
            }
            return oServer.Databases[this.DatabaseName];
        }

        public Microsoft.SqlServer.Management.Smo.Server GetSmoServer()
        {
            Microsoft.SqlServer.Management.Smo.Server oServer = null;
            if (!System.String.IsNullOrEmpty(this.UserName))
            {
                Microsoft.SqlServer.Management.Common.ServerConnection oConn =
                    new Microsoft.SqlServer.Management.Common.ServerConnection(this.DataSourceName,
                        this.UserName, this.Password);
                oConn.ConnectTimeout = 5;
                oConn.Connect();
                oServer = new Microsoft.SqlServer.Management.Smo.Server(oConn);
            }
            else
            {

                oServer = new Microsoft.SqlServer.Management.Smo.Server(this.DataSourceName);
            }
            if (oServer == null)
                throw new Exception("Could not connect to SMO server " + this.DataSourceName);
            return oServer;
        }


        public bool IsDbOwner()
        {
            return GetSmoDatabase(GetSmoServer()).IsDbOwner;
        }
    }
}
